﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SQLite;
using System.Data;

namespace RaiderManagerBackend
{
    class SQLiteCommunicator
    {
        private SQLiteConnection con;
        private SQLiteCommand cmd;
        private SQLiteDataAdapter DB;
        

        private void SetConnection()
        {
            con = new SQLiteConnection("Data Source=../../../Database/BCRaidTracker.db;Version=3;New=False;Compress=True;");
        }

        private string ExecuteQuery(string txtQuery)
        {
            string ret = string.Empty;
            try
            {
                SetConnection();
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = txtQuery;
                ret = cmd.ExecuteScalar().ToString();
            }
            finally
            {
                con.Close();
            }
            return ret;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName">The name of the table for which we want to insert.</param>
        /// <param name="ColumnValuePairs">The values to insert into the table. These are set in pairs such as "RaidId=11111"</param>
        /// <returns>The rowid for this insert</returns>
        private int Insert(string tableName, params string[] ColumnValuePairs)
        {
            string columnString = "";
            string valueString = "";

            foreach (string pair in ColumnValuePairs)
            {
                string[] tString = pair.Split('=');
                columnString += tString[0] + ", ";
                valueString += "'" + tString[1] + "'" + ", ";
            }

            //remove any additional commas
            columnString = columnString.Remove(columnString.LastIndexOf(','));
            valueString = valueString.Remove(valueString.LastIndexOf(','));

            string tempSql = string.Format("insert into {0} ({1}) values ({2});", tableName, columnString, valueString);

            int ret = 0;

            try
            {
                SetConnection();
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = tempSql + "select last_insert_rowid() AS [ID]";
                ret = int.Parse(cmd.ExecuteScalar().ToString());

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
                
            }
            return ret;
        }

        private void Update(string updateStatement)
        {
            try
            {
                SetConnection();
                con.Open();
                cmd = con.CreateCommand();
                cmd.CommandText = updateStatement;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }
            finally
            {
                con.Close();
            }
        }

        public int AddUpdatePlayer(Player p)
        {
            if (p.PlayerId == 0)
            {
                //new player.
                int PlayerId = Insert("Player", "Name=" + p.Name, "OverallGrade=" + p.OverallGrade, "PerformanceGrade=" + p.PerformanceGrade, "AtttendanceGrade=" + p.AttendanceGrade,
                    "OfficerGrade=" + p.OfficerGrade, "GuildRank=" + p.GuildRank);
                return PlayerId;
            }
            else
            {
                //update our current player
                Update(string.Format("Update player set Name = '{0}', OverallGrade = '{1}', PerformanceGrade = '{2}', AtttendanceGrade = '{3}', " +
                    "OfficerGrade = '{4}', GuildRank = '{5}' where PlayerId = {6};",
                    p.Name, p.OverallGrade, p.PerformanceGrade, p.AttendanceGrade, p.OfficerGrade, p.GuildRank, p.PlayerId));
                return p.PlayerId;
            }
        }

        public int AddUpdateRaid(Raid r)
        {
            if (r.RaidId == 0)
            {
                int RaidId = Insert("Raid", "Date=" + r.Date.ToShortDateString());
                return RaidId;
            }
            else
            {
                Update(string.Format("Update Raid set Date = '{0}' where RaidId = {1}", r.Date.ToShortDateString(), r.RaidId));
                return r.RaidId;
            }
        }

        public int AddUpdateEncounter(Encounter e)
        {
            if (e.EncounterId == 0)
            {
                int encounterId = Insert("Encounter", "Boss=" + e.BossName, "Location=" + e.Location, "RaidId=" + e.Raid.RaidId.ToString());
                return encounterId;
            }
            else
            {
                Update(string.Format("Update Encounter set BossName = '{0}', RaidId = {1}, Location = {2} where EncounterId = {3}", 
                    e.BossName, e.Raid.RaidId, e.Location, e.EncounterId));
                return e.EncounterId;
            }
        }

        public int AddUpdateAttendanceEntry(AttendanceEntry ae)
        {
            if (ae.AttendanceEntryId == 0)
            {
                int entryId = Insert("AttendanceEntry", "AttendanceCode=" + ae.AttendanceCode, "SignupCode=" + ae.SignUpCode,
                    "PlayerId=" + ae.Player.PlayerId, "RaidId=" + ae.Raid.RaidId);
                return entryId;
            }
            else
            {
                Update(string.Format("Update AttendanceEntry set AttendanceCode = '{0}', 
            }
        }

        public void BuildPlayers()
        {
            DataSet DS = new DataSet();
            DataTable PlayersDataTable = new DataTable();

            SetConnection();
            try
            {
                con.Open();
                cmd = con.CreateCommand();
                string CommandText = "select * from Player";
                DB = new SQLiteDataAdapter(CommandText, con);
                DB.Fill(DS);
                PlayersDataTable = DS.Tables[0];
                foreach (DataRow row in PlayersDataTable.Rows)
                {
                    Player p = new Player();
                    p.PlayerId = int.Parse(row["PlayerId"].ToString());
                    p.Name = (string)row["Name"];
                    p.OverallGrade = row["OverallGrade"].ToString();
                    p.PerformanceGrade = row["PerformanceGrade"].ToString();
                    p.AttendanceGrade = row["AtttendanceGrade"].ToString();
                    p.OfficerGrade = row["OfficerGrade"].ToString();
                    p.GuildRank = row["GuildRank"].ToString();
                }
            }
            finally
            {
                con.Close();
            }
        }
    }
}
